/*
    libhdvbd - Library for building sophisticated DVB applications
    Copyright (C) 2012 Tony Houghton <h@realh.co.uk>

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as published by
    the Free Software Foundation; either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

#include <glib.h>

#include <stdio.h>
#include <string.h>

#include "dvbenc.h"
#include "eitdb.h"
#include "freesat-decode.h"
#include "dbcommon.h"
#include "i18n.h"


static void
create_events_table(sqlite3 *sql)
{
    hdvb_db_exec(sql, "DROP TABLE IF EXISTS events");
    hdvb_db_exec(sql,
            "CREATE TABLE events ("
            "    service_id          INT NOT NULL,"
            "    ts_id               INT NOT NULL,"
            "    network_id          INT NOT NULL,"
            "    event_id            INT NOT NULL,"
            "    start_time          INT NOT NULL,"
            "    duration            INT NOT NULL,"
            "    scrambled           INT NULL,"
            "    stamp               INT NOT NULL,"
            "    PRIMARY KEY (service_id, event_id)"
            ")");
}

static void
create_short_descriptors_table(sqlite3 *sql)
{
    /*hdvb_db_exec(sql, "DROP INDEX IF EXISTS short_descriptors_ids");*/
    hdvb_db_exec(sql, "DROP TABLE IF EXISTS short_descriptors");
    hdvb_db_exec(sql,
            "CREATE TABLE short_descriptors ("
            "    service_id          INT NOT NULL,"
            "    event_id            INT NOT NULL,"
            "    language            INT NOT NULL," /* Really a 3 char string */
            "    event_name          BLOB NULL,"
            "    event_text          BLOB NULL,"
            "    PRIMARY KEY (service_id, event_id),"
            "    FOREIGN KEY (service_id, event_id)"
            "    REFERENCES events (service_id, event_id)"
            ")");
    /*
    hdvb_db_exec(sql,
            "CREATE INDEX short_descriptors_ids ON"
            "    short_descriptors (service_id, event_id)");
    */
}

static void
create_time_shifted_events_table(sqlite3 *sql)
{
    hdvb_db_exec(sql,
            "DROP INDEX IF EXISTS time_shifted_events_ids;"
            "DROP TABLE IF EXISTS time_shifted_events; "
            "CREATE TABLE time_shifted_events ("
            "    service_id          INT NOT NULL,"
            "    event_id            INT NOT NULL,"
            "    ref_service_id      INT NOT NULL,"
            "    ref_event_id        INT NOT NULL"
            ");"
            "CREATE INDEX time_shifted_events_ids ON "
            "    time_shifted_events (ref_service_id, ref_event_id);");
}

static void
create_trigger(sqlite3 *sql)
{
    hdvb_db_exec(sql,
            "CREATE TRIGGER IF NOT EXISTS del_event BEFORE DELETE ON events"
            "   FOR EACH ROW "
            "   BEGIN"
            "       DELETE FROM short_descriptors"
            "           WHERE service_id = old.service_id"
            "               AND event_id = old.event_id;"
            "       DELETE FROM time_shifted_events"
            "           WHERE service_id = old.service_id"
            "               AND event_id = old.event_id;"
            "       DELETE FROM time_shifted_events"
            "           WHERE ref_service_id = old.service_id"
            "               AND ref_event_id = old.event_id;"
            "   END");
}

static void
create_encoding_table(sqlite3 *sql, const char *encoding)
{
    /* FIXME: Should use ? instead of building INSERT statement this way */
    char *stmt = g_strdup_printf(
            "DROP TABLE IF EXISTS encoding; "
            "CREATE TABLE encoding (encoding TEXT); "
            "INSERT INTO encoding (encoding) VALUES ('%s');",
            encoding ? encoding : "dvb");
    
    hdvb_db_exec(sql, stmt);
    g_free(stmt);
}

void
hdvb_eit_db_create_schemas(sqlite3 *sql, gpointer handle)
{
    create_encoding_table(sql, handle);
    create_events_table(sql);
    create_short_descriptors_table(sql);
    create_time_shifted_events_table(sql);
    create_trigger(sql);
}

sqlite3_stmt *
hdvb_eit_db_statement_insert_event(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO events "
            "   (service_id, ts_id, network_id, event_id, "
            "   start_time, duration, scrambled, stamp) "
            "   VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
}

sqlite3_stmt *
hdvb_eit_db_statement_insert_short_descriptor(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO short_descriptors "
            "   (service_id, event_id, "
            "   language, event_name, event_text) "
            "   VALUES (?, ?, ?, ?, ?)");
}

sqlite3_stmt *
hdvb_eit_db_statement_insert_time_shifted_event(sqlite3 *sql)
{
    return hdvb_db_prepare_statement(sql,
            "INSERT OR REPLACE INTO time_shifted_events "
            "   (service_id, event_id, "
            "   ref_service_id, ref_event_id)"
            "   VALUES (?, ?, ?, ?)");
}

void
hdvb_eit_db_event_details_free(HdvbEitDbEventDetails *details)
{
    g_free(details->event_text);
    g_free(details->event_name);
    g_slice_free(HdvbEitDbEventDetails, details);
}

void
hdvb_eit_db_event_details_free_list(GList *list)
{
    GList *link;
    
    for (link = list; link; link = g_list_next(link))
        hdvb_eit_db_event_details_free(link->data);
    g_list_free(list);
}

static GList *
fix_clashes(GList *results)
{
    GList *link = results;
    GList *prev;
    GList *next;
    
    while (link)
    {
        next = g_list_next(link);
        prev = g_list_previous(link);
        if (prev)
        {
            HdvbEitDbEventDetails *d1 = prev->data;
            HdvbEitDbEventDetails *d2 = link->data;
            time_t end1 = d1->start_time + d1->duration;
            time_t end2 = d2->start_time + d2->duration;
            
            if (end1 > d2->start_time)
            {
                if (d1->stamp > d2->stamp)
                {
                    if (end1 >= end2)
                    {
                        hdvb_eit_db_event_details_free(d2);
                        results = g_list_delete_link(results, link);
                    }
                    else
                    {
                        d2->start_time = end1;
                        d2->duration = end2 - end1;
                    }
                }
                else
                {
                    if (d2->start_time <= d1->start_time)
                    {
                        hdvb_eit_db_event_details_free(d1);
                        results = g_list_delete_link(results, prev);
                    }
                    else
                    {
                        d1->duration = d2->start_time - d1->start_time;
                    }
                }
            }
        }
        link = next;
    }
    return results;
}

static HdvbEitDbEventDetails *
event_details_from_row(sqlite3_stmt *stmt, gboolean full_details)
{
    HdvbEitDbEventDetails *details = g_slice_new(HdvbEitDbEventDetails);
    const guchar *blob;
    
    details->service_id = sqlite3_column_int(stmt, 0);
    details->event_id = sqlite3_column_int(stmt, 1);
    details->start_time = sqlite3_column_int64(stmt, 2);
    details->duration = (time_t) sqlite3_column_int(stmt, 3);
    details->scrambled = sqlite3_column_int(stmt, 4);
    details->stamp = sqlite3_column_int64(stmt, 5);
    blob = sqlite3_column_text(stmt, 6);
    if (blob && blob[0])
        details->event_name = g_strdup((const char *) blob);
    else
        details->event_name = NULL;
    if (full_details)
    {
        int *punned = (int *) details->lang;
        
        blob = sqlite3_column_text(stmt, 7);
        if (blob && blob[0])
            details->event_text = g_strdup((const char *) blob);
        else
            details->event_text = NULL;
        *punned = sqlite3_column_int(stmt, 8);
    }
    else
    {
        details->event_text = NULL;
        details->lang[0] = 0;
    }
    return details;
}

HdvbEitDbEventDetails *
hdvb_eit_db_event_details_get(sqlite3 *sql, int service_id, int event_id)
{
    char *stmt_s = g_strdup_printf(
            "SELECT events.service_id, events.event_id,"
            "   start_time, duration, scrambled, stamp, "
            "   event_name, event_text, language "
            "FROM events, short_descriptors "
            "WHERE events.service_id = ?"
            "   AND events.event_id = ?"
            "   AND events.event_id = short_descriptors.event_id"
            "   AND events.service_id = short_descriptors.service_id");
    sqlite3_stmt *stmt = hdvb_db_prepare_statement(sql, stmt_s);
    HdvbEitDbEventDetails *details = NULL;
    
    g_free(stmt_s);
    if (!stmt)
        return NULL;
    sqlite3_bind_int(stmt, 1, service_id);
    sqlite3_bind_int(stmt, 2, event_id);
    switch (sqlite3_step(stmt))
    {
        case SQLITE_ROW:
            details = event_details_from_row(stmt, TRUE);
            break;
        case SQLITE_DONE:
            g_warning(_("No details for event %d on service %d"),
                    event_id, service_id);
            break;
        default:
            g_warning(_("Error looking up event %d on service %d: %s"),
                    event_id, service_id, sqlite3_errmsg(sql));
            break;
    }
    sqlite3_finalize(stmt);
    return details;
}

GList *
hdvb_eit_db_list_events(sqlite3 *sql, int service_id,
        gboolean full_details, time_t start_time, time_t end_time)
{
    GList *results = NULL;
    char *after = start_time ? g_strdup_printf(
            "AND (start_time + duration) > %ld ", start_time) : NULL;
    char *before = end_time ? g_strdup_printf(
            "AND start_time < %ld ", end_time) : NULL;
    char *stmt_s = g_strdup_printf(
            "SELECT events.service_id, events.event_id,"
            "   start_time, duration, scrambled, stamp, "
            "   event_name%s "
            "FROM events, short_descriptors "
            "WHERE events.service_id = ?"
            "   AND events.service_id = short_descriptors.service_id"
            "   AND events.event_id = short_descriptors.event_id "
            "%s%s"
            "ORDER BY start_time",
            full_details ? ", event_text, language" : "",
            after ? after : "", before ? before : "");
    sqlite3_stmt *stmt = hdvb_db_prepare_statement(sql, stmt_s);
    int sql_result;
    
    g_free(stmt_s);
    g_free(after);
    g_free(before);
    if (!stmt)
        return NULL;
    sqlite3_bind_int(stmt, 1, service_id);
    while ((sql_result = sqlite3_step(stmt)) != SQLITE_DONE)
    {
        if (sql_result == SQLITE_ROW)
        {
            results = g_list_append(results,
                    event_details_from_row(stmt, full_details));
        }
        else
        {
            hdvb_db_complain(sql, sql_result, _("EIT query failed"));
            break;
        }
    }
    sqlite3_finalize(stmt);
    results = fix_clashes(results);
    return results;
}

void
hdvb_eit_db_remove_old_events(sqlite3 *sql, time_t tm)
{
    sqlite3_stmt *stmt = hdvb_db_prepare_statement(sql,
            "DELETE FROM events WHERE start_time + duration < ?");
    int result;
    
    if (!stmt)
        return;
    sqlite3_bind_int64(stmt, 1, (sqlite3_int64) tm);
    while ((result = sqlite3_step(stmt)) == SQLITE_ROW);
    if (result != SQLITE_DONE)
    {
        g_warning(_("Database error deleting old events: %s"),
                sqlite3_errmsg(sql));
    }
    sqlite3_finalize(stmt);
}

void
hdvb_eit_db_str_decoder(sqlite3_context *sctx, int n, sqlite3_value **argv)
{
    sqlite3_result_text(sctx,
            hdvb_dvb_str_to_utf8(sqlite3_value_blob(argv[0]),
                    sqlite3_value_bytes(argv[0])),
            -1,
            g_free);
}

static void
hdvb_eit_db_freesat_str_decoder(sqlite3_context *sctx, int n,
        sqlite3_value **argv)
{
    sqlite3_result_text(sctx,
            hdvb_freesat_str_to_utf8(sqlite3_value_blob(argv[0]),
                    sqlite3_value_bytes(argv[0])),
            -1,
            g_free);
}

static void
hdvb_eit_db_utf8_str_identity(sqlite3_context *sctx, int n,
        sqlite3_value **argv)
{
    const guchar *blob = sqlite3_value_blob(argv[0]);
    int len = sqlite3_value_bytes(argv[0]);
    char *text = g_malloc(len + 1);
    
    memcpy(text, blob, len);
    text[len] = 0;
    sqlite3_result_text(sctx, text, -1, g_free);
}

gboolean
hdvb_eit_db_setup_decoder(sqlite3 *sql)
{
    int result;
    void (*xfunc)(sqlite3_context *, int, sqlite3_value **);
    const char *encoding;
    sqlite3_stmt *stmt = hdvb_db_prepare_statement(sql,
            "SELECT encoding FROM encoding");
    
    if (hdvb_db_step(stmt) != SQLITE_ROW)
        return FALSE;
    encoding = (const char *) sqlite3_column_text(stmt, 0);
    if (!strcmp(encoding, "freesat") || !strcmp(encoding, "freeviewhd"))
        xfunc = hdvb_eit_db_freesat_str_decoder;
    else if (!strcmp(encoding, "utf8"))
        xfunc = hdvb_eit_db_utf8_str_identity;
    else
        xfunc = hdvb_eit_db_str_decoder;
    sqlite3_finalize(stmt);
    result = sqlite3_create_function_v2(sql,
            "hdvb_decode", 1, SQLITE_ANY, NULL, xfunc, NULL, NULL, NULL);
    if (result == SQLITE_OK)
    {
        return TRUE;
    }
    else
    {
        hdvb_db_complain(sql, result,
                _("Unable to create sqlite3 function to decode EIT strings"));
        return FALSE;
    }
}

static char *
hdvb_eit_db_identity(const guchar *blob, int len)
{
    char *text = g_malloc(len + 1);
    
    memcpy(text, blob, len);
    text[len] = 0;
    return text;
}

HdvbEitDbDecoder
hdvb_eit_db_get_decoder(sqlite3 *sql)
{
    const char *encoding;
    sqlite3_stmt *stmt = hdvb_db_prepare_statement(sql,
            "SELECT encoding FROM encoding");
    
    if (hdvb_db_step(stmt) != SQLITE_ROW)
        return FALSE;
    encoding = (const char *) sqlite3_column_text(stmt, 0);
    if (!strcmp(encoding, "freesat"))
        return hdvb_freesat_str_to_utf8;
    else if (!strcmp(encoding, "utf8"))
        return hdvb_eit_db_identity;
    return hdvb_dvb_str_to_utf8;
}
